Dim FileValid
Dim xmlFileName
Dim xsdFileName

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")

' ############################################################################################
' ########################## 		INSTRUCTIONS		    ########################## 
' ########################## Enter SQLServer Name and Database Name ########################## 
' #   IN the line below, replace the Computer_name\SQL_SERVER_NAME and 			     #
' #	DESTINATION_DB_NAME to match your SQL installation:  	     			     #
' #		data source=COMPUTER_NAME\SQL_SERVER_NAME;				     #
' #		database=DESTINATION_DB_NAME;						     #
' #											     #
' Example: objBL.ConnectionString = "provider=SQLOLEDB;data source=BENEDICT-LAPTOP\SQLEXPRESS;database=CAFASData;integrated security=SSPI"
' ############################################################################################

objBL.ConnectionString = "provider=SQLOLEDB;data source=COMPUTER_NAME\SQL_SERVER_NAME;database=DESTINATION_DB_NAME;integrated security=SSPI"

' ############################################################################################
' ########################## 		INSTRUCTIONS		    ########################## 
' ##########################       Enter XML Source Filename        ########################## 
' #   In the line below, replace the CAFAS_ExportData.xml with the name of your XML source file    #
' #		xmlFileName = "CAFAS_ExportData.xml"					     #
' ############################################################################################

xmlFileName = "CAFAS_ExportData.xml"

objBL.ErrorLogFile = "error.log"
xsdFileName = "CAFAS_XSDFormat.xml"

'Validate the data file prior to bulkload
Dim sOutput 
sOutput = ValidateFile(xmlFileName, "", xsdFileName)
WScript.Echo sOutput

If FileValid Then
   ' Check constraints and initiate transaction (if needed)
   ' objBL.CheckConstraints = True
   ' objBL.Transaction=True

objBL.CheckConstraints=true
objBL.XMLFragment = True
objBL.SchemaGen = True
' ############################################################################################
' ########################## 		INSTRUCTIONS		    ########################## 
' ########################## SGDropTables Flag to Either: 	    ########################## 
' #											     #
' #				objBL.SGDropTables = True				     #
' #					Delete tables and re-create with import data	     #
' #											     #
' #				objBL.SGDropTables = False				     #
' #					Append data to current data in tables.  	     #
' #					NOTE: Be careful not to import duplicate data!	     #
' ############################################################################################
objBL.SGDropTables = False

  'Execute XML bulkload using file.
  objBL.Execute xsdFileName, xmlFileName
  set objBL=Nothing
End If

Function ValidateFile(strXmlFile,strUrn,strXsdFile)
    
   ' Create a schema cache and add SampleSchema.xml to it.
   Dim xs, fso, sAppPath
   Set fso = CreateObject("Scripting.FileSystemObject") 
   Set xs = CreateObject("MSXML2.XMLSchemaCache.6.0")
   sAppPath = fso.GetFolder(".") 
   xs.Add strUrn, sAppPath & "\" & strXsdFile

   ' Create an XML DOMDocument object.
   Dim xd 
   Set xd = CreateObject("MSXML2.DOMDocument.6.0")

   ' Assign the schema cache to the DOM document.
   ' schemas collection.
   Set xd.schemas = xs

   ' Load XML document as DOM document.
   xd.async = False
   xd.Load sAppPath & "\" & strXmlFile

   ' Return validation results in message to the user.
   If xd.parseError.errorCode <> 0 Then
        ValidateFile = "Validation failed on " & _
             strXmlFile & vbCrLf & _
             "=====================" & vbCrLf & _
             "Reason: " & xd.parseError.reason & _
             vbCrLf & "Source: " & _
             xd.parseError.srcText & _
             vbCrLf & "Line: " & _
             xd.parseError.Line & vbCrLf
             FileValid = False
    Else
        ValidateFile = "Validation succeeded for " & _
             strXmlFile & vbCrLf & _
             "======================" & _
             vbCrLf & vbCrLf & "Click OK to load CAFAS Data." & vbCrLf
             FileValid = True

    End If
End Function